package com.truthbean.jeetb.model.sys.repository;

import com.truthbean.jeetb.model.sys.entity.User;

import java.sql.*;
import java.util.logging.Logger;

/**
 * Created by TruthBean on 2016-10-22 16:23.
 */
public class UserDao {
    private static final String SQLITE3 = "model/src/main/resources/META-INF/databases/jeetb.sqlite3";

    private Logger logger = Logger.getLogger(getClass().getName());

    Connection conn = null;

    public UserDao() {
        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:" + SQLITE3);
            conn.setAutoCommit(false);
        } catch (Exception e) {
            logger.severe(e.getClass().getName() + ": " + e.getMessage());
        }
        System.out.println("Opened database successfully");
    }

    public int insert(User user) throws SQLException {
        int result = 0;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "INSERT INTO `jeetb_user`(name, email, password, `group`) VALUES (?, ?, ?, ?)";
        try {
            /**
             * Statement.RETURN_GENERATED_KEYS 获取自动生成的键
             */
            pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getEmail());
            pstmt.setString(3, user.getPassword());
            pstmt.setString(4, user.getGroup());
            pstmt.executeUpdate();
            rs = pstmt.getGeneratedKeys();
            /**
             * autoGeneratedKeys - 指示是否应该返回自动生成的键?
             * Statement.RETURN_GENERATED_KEYS ? Statement.NO_GENERATED_KEYS 之一
             */
            if (rs.next()) {
                result = rs.getInt(1);
            }
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            logger.severe(e.getMessage());
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return result;
    }

    public int update(User user) throws SQLException {
        int result = 0;
        PreparedStatement pstmt = null;
        String sql = "UPDATE `jeetb_user` SET `name` = ?, `email` = ?, `password` = ?, `group` = ? WHERE `id` = ?";
        try {
            /**
             * Statement.RETURN_GENERATED_KEYS 获取自动生成的键
             */
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getEmail());
            pstmt.setString(3, user.getPassword());
            pstmt.setString(4, user.getGroup());
            pstmt.setInt(5, user.getId());
            result = pstmt.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            logger.severe(e.getMessage());
        } finally {
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return result;
    }

    public User selectById(int id) throws SQLException {
        PreparedStatement pstmt = null;
        String sql = "SELECT `id`, `name`, `email`, `password`, `group` FROM `jeetb_user` WHERE `id` = ?";
        ResultSet rs = null;
        User result = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                result = new User();
                result.setId(rs.getInt(1));
                result.setName(rs.getString(2));
                result.setEmail(rs.getString(3));
                result.setPassword(rs.getString(4));
                result.setGroup(rs.getString(5));
            }
        } catch (SQLException e) {
            logger.severe(e.getMessage());
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return result;
    }

    public User selectByEmailOrNameAndPassword(String emailOrName, String password) throws SQLException {
        PreparedStatement pstmt = null;
        String sql = "SELECT `id`, `name`, `email`, `password`, `group` FROM `jeetb_user` WHERE (`name` = ? OR `email` = ?) AND `password` = ?";
        ResultSet rs = null;
        User result = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, emailOrName);
            pstmt.setString(2, emailOrName);
            pstmt.setString(3, password);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                result = new User();
                result.setId(rs.getInt(1));
                result.setName(rs.getString(2));
                result.setEmail(rs.getString(3));
                result.setPassword(rs.getString(4));
                result.setGroup(rs.getString(5));
            }
        } catch (SQLException e) {
            logger.severe(e.getMessage());
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return result;
    }
}
